package dal;

import hist_data.DailyBar;
import hist_data.DailyPair;
import hist_data.MinuteBar;
import iblink.core.Stock;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.jdbc.datasource.DataSourceUtils;

import packutil.EtcUtil;

public class DataAccessor implements IAppDataAccessor, IHistoricalDataAccessor {

	private DataSource dataSource;

	// TODO: do i need this constructor now that im not using proxies?
	protected DataAccessor() {
	}

	public DataAccessor(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	@Override
	public synchronized Date getLastProcessedDate(String processName) {

		try {

			Connection c = DataSourceUtils.getConnection(dataSource);

			String proc = "{ call at_app.get_last_processed_date(?) }";
			CallableStatement cs = c.prepareCall(proc);
			cs.setString(1, processName);
			cs.execute();
			ResultSet rs = cs.getResultSet();

			if (!rs.next())
				return null;

			Date d = rs.getDate(1);
			DataSourceUtils.releaseConnection(c, dataSource);

			return d;

		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

	@Override
	public synchronized void recordProcessBegin(String processName, Date date) {

		try {
			Connection c = DataSourceUtils.getConnection(dataSource);

			String proc = "{ call at_app.record_process_begin(?,?) }";
			CallableStatement cs = c.prepareCall(proc);
			cs.setString(1, processName);
			cs.setDate(2, new java.sql.Date(date.getTime()));
			cs.execute();
			// cs.close();
			DataSourceUtils.releaseConnection(c, dataSource);

		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

	@Override
	public synchronized void recordProcessComplete(String processName) {

		try {

			Connection c = DataSourceUtils.getConnection(dataSource);
			String proc = "{ call at_app.record_process_complete(?,?) }";
			CallableStatement cs = c.prepareCall(proc);
			cs.setString(1, processName);
			cs.registerOutParameter(2, java.sql.Types.BOOLEAN);
			cs.execute();
			boolean success = cs.getBoolean(2);
			DataSourceUtils.releaseConnection(c, dataSource); // c.close();

			if (!success) {
				throw new RuntimeException(
						"Error marking process complete. Couldn't find process with specified name that wasn't already complete");
			}

		} catch (Exception e) {
			throw new RuntimeException(e);
		}

	}

	@Override
	public synchronized void recordProcessError(String processName, Exception ex) {

		final int errorMsgDatabaseFieldLength = 300;
		String errMsg = EtcUtil.getStackTrace(ex);

		if (errMsg.length() > errorMsgDatabaseFieldLength)
			errMsg = errMsg.substring(0, errorMsgDatabaseFieldLength);

		try {

			Connection c = DataSourceUtils.getConnection(dataSource);

			// TODO: make this final and move to top of method for all methods
			String proc = "{ call at_app.record_process_error(?,?,?) }";
			CallableStatement cs = c.prepareCall(proc);
			cs.setString(1, processName);
			cs.setString(2, errMsg);
			cs.registerOutParameter(3, java.sql.Types.BOOLEAN);
			cs.execute();
			boolean success = cs.getBoolean(3);
			DataSourceUtils.releaseConnection(c, dataSource); // c.close();

			if (!success) {
				throw new RuntimeException(
						"Error marking process error. Couldn't find process with specified name that wasn't already complete");
			}

		} catch (Exception e) {
			throw new RuntimeException(e);
		}

	}

	@Override
	public synchronized List<Date> getNyseHolidays() {

		List<Date> holidays = new ArrayList<Date>();

		try {

			Connection c = DataSourceUtils.getConnection(dataSource);
			String proc = "{ call at_app.get_nyse_holidays }";
			CallableStatement cs = c.prepareCall(proc);
			cs.execute();
			ResultSet rs = cs.getResultSet();

			while (rs.next()) {
				holidays.add(rs.getDate(1));
			}

			DataSourceUtils.releaseConnection(c, dataSource); // c.close();

		} catch (Exception e) {
			throw new RuntimeException(e);
		}

		return holidays;
	}

	public void saveDailyBar(Stock stock, DailyBar dailyBar) {

		try {

			Connection c = DataSourceUtils.getConnection(dataSource);
			String saveHistBarProc = "{ call hist_data.add_daily_bar(?,?,?,?,?,?,?,?,?) }";

			CallableStatement cs = c.prepareCall(saveHistBarProc);
			cs.setString(1, stock.getSymbol());
			cs.setTimestamp(2, new java.sql.Timestamp(dailyBar.getDate()
					.getTime()));
			cs.setDouble(3, dailyBar.getOpen());
			cs.setDouble(4, dailyBar.getHigh());
			cs.setDouble(5, dailyBar.getLow());
			cs.setDouble(6, dailyBar.getClose());
			cs.setInt(7, dailyBar.getCount());
			cs.setInt(8, dailyBar.getVolume());
			cs.setDouble(9, dailyBar.getActivityPercent());
			cs.execute();

			DataSourceUtils.releaseConnection(c, dataSource); // c.close();

		} catch (Exception e) {
			throw new RuntimeException(e);
		}

	}

	@Override
	public void saveMinuteBars(Stock stock, List<MinuteBar> dataPoints) {

		if (dataPoints.isEmpty())
			return;

		try {

			Connection c = DataSourceUtils.getConnection(dataSource);
			String saveHistBarProc = "{ call hist_data.add_minute_bar(?,?,?,?,?,?,?,?,?,?,?,?,?) }";

			for (MinuteBar mb : dataPoints) {

				CallableStatement cs = c.prepareCall(saveHistBarProc);
				cs.setString(1, stock.getSymbol());
				cs.setTimestamp(2, new java.sql.Timestamp(mb.getDate()
						.getTime()));
				cs.setDouble(3, mb.getOpen());
				cs.setDouble(4, mb.getHigh());
				cs.setDouble(5, mb.getLow());
				cs.setDouble(6, mb.getClose());
				cs.setDouble(7, mb.getWAP());
				cs.setInt(8, mb.getCount());
				cs.setInt(9, mb.getVolume());
				cs.setDouble(10, mb.getSodChange());
				cs.setDouble(11, mb.getOneBarChange());
				cs.setDouble(12, mb.getFiveBarChange());
				cs.setDouble(13, mb.getTenBarChange());

				cs.execute();
			}

			DataSourceUtils.releaseConnection(c, dataSource); // c.close();

		} catch (Exception e) {
			throw new RuntimeException(e);
		}

	}

	@Override
	public List<Stock> getUnprocessedStocks(Date date) {

		System.out.println("getUnprocessedStocks");

		List<Stock> stocks = new ArrayList<Stock>();

		try {

			Connection c = DataSourceUtils.getConnection(dataSource);

			String proc = "{ call hist_data.get_unprocessed_stocks(?) }";
			CallableStatement cs = c.prepareCall(proc);
			cs.setDate(1, new java.sql.Date(date.getTime()));
			cs.execute();
			ResultSet rs = cs.getResultSet();

			while (rs.next()) {
				stocks.add(parseStockFromRow(rs));
			}

			System.out.println("got " + stocks.size());

			DataSourceUtils.releaseConnection(c, dataSource); // c.close();

		} catch (Exception e) {
			throw new RuntimeException(e);
		}

		return stocks;
	}

	@Override
	public List<Stock> getProcessedStocks(Date date) {

		List<Stock> stocks = new ArrayList<Stock>();

		try {

			Connection c = DataSourceUtils.getConnection(dataSource);

			String proc = "{ call hist_data.get_processed_stocks(?) }";
			CallableStatement cs = c.prepareCall(proc);
			cs.setDate(1, new java.sql.Date(date.getTime()));
			cs.execute();
			ResultSet rs = cs.getResultSet();

			while (rs.next()) {
				stocks.add(parseStockFromRow(rs));
			}

			DataSourceUtils.releaseConnection(c, dataSource);

		} catch (Exception e) {
			throw new RuntimeException(e);
		}

		return stocks;
	}

	private MinuteBar parseMinuteBarFromRow(ResultSet rs) throws SQLException {

		Date barTime = rs.getTimestamp("bar_time");
		double open = rs.getDouble("open");
		double high = rs.getDouble("high");
		double low = rs.getDouble("low");
		double close = rs.getDouble("close");
		double wap = rs.getDouble("wap");
		int count = rs.getInt("count");
		int volume = rs.getInt("volume");

		return new MinuteBar(barTime, open, high, low, close, volume, count,
				wap);
	}

	private Stock parseStockFromRow(ResultSet rs) throws SQLException {
		String symbol = rs.getString("symbol");
		String longName = rs.getString("long_name");
		String industry = rs.getString("industry");
		String category = rs.getString("category");
		String subCategory = rs.getString("subcategory");
		String primaryExchange = rs.getString("primary_exchange");
		return new Stock(symbol, longName, industry, category, subCategory,
				primaryExchange);
	}

	@Override
	public void addUpdateStock(Stock stk) {
		try {
			Connection c = DataSourceUtils.getConnection(dataSource);
			String proc = "{ call hist_data.add_update_stock(?,?,?,?,?,?) }";
			CallableStatement cs = c.prepareCall(proc);
			cs.setString(1, stk.getSymbol());
			cs.setString(2, stk.getLongName());
			cs.setString(3, stk.getIndustry());
			cs.setString(4, stk.getCategory());
			cs.setString(5, stk.getSubCategory());
			cs.setString(6, stk.getPrimaryExchange());
			cs.execute();

			DataSourceUtils.releaseConnection(c, dataSource);

		} catch (Exception e) {
			// TODO: throw better exceptions for these.. DataAccess/or/Exception
			// ?
			throw new RuntimeException(e);
		}
	}

	@Override
	public MinuteBar getLastBar(Stock stock, Date date) {

		MinuteBar retVal = null;

		try {

			Connection c = DataSourceUtils.getConnection(dataSource);
			String proc = "{ call hist_data.get_last_minute_bar(?,?) }";
			CallableStatement cs = c.prepareCall(proc);
			cs.setString(1, stock.getSymbol());
			cs.setDate(2, new java.sql.Date(date.getTime()));
			cs.execute();
			ResultSet rs = cs.getResultSet();

			int rowCt = 0;
			while (rs.next()) {
				if (++rowCt > 1)
					throw new RuntimeException("Only expecting one row!");

				retVal = parseMinuteBarFromRow(rs);
			}
			DataSourceUtils.releaseConnection(c, dataSource); // c.close();

		} catch (Exception e) {
			throw new RuntimeException(e);
		}

		if (retVal == null)
			throw new RuntimeException("error getting last bar for stock "
					+ stock + " and date " + date);

		return retVal;
	}

	@Override
	public List<MinuteBar> getMinuteBars(Stock stock, Date date) {

		List<MinuteBar> retVal = new ArrayList<MinuteBar>();

		try {

			Connection c = DataSourceUtils.getConnection(dataSource);
			String proc = "{ call hist_data.get_minute_bars(?,?) }";
			CallableStatement cs = c.prepareCall(proc);
			cs.setString(1, stock.getSymbol());
			cs.setDate(2, new java.sql.Date(date.getTime()));
			cs.execute();
			ResultSet rs = cs.getResultSet();

			while (rs.next()) {
				MinuteBar mb = parseMinuteBarFromRow(rs);
				retVal.add(mb);
			}
			DataSourceUtils.releaseConnection(c, dataSource);

		} catch (Exception e) {
			throw new RuntimeException(e);
		}

		return retVal;
	}

	@Override
	public void saveDailyPair(DailyPair dp) {

		try {
			Connection c = DataSourceUtils.getConnection(dataSource);
			String proc = "{ call hist_data.add_daily_pair(?,?,?,?) }";
			CallableStatement cs = c.prepareCall(proc);
			cs.setString(1, dp.getStock1().getSymbol());
			cs.setString(2, dp.getStock2().getSymbol());
			cs.setDate(3, new java.sql.Date(dp.getDate().getTime()));
			cs.setDouble(4, dp.getOneDayCorrelation());
			cs.execute();

			DataSourceUtils.releaseConnection(c, dataSource);

		} catch (Exception e) {
			// TODO: throw better exceptions for these.. DataAccess/or/Exception
			// ?
			throw new RuntimeException(e);
		}
	}

	@Override
	public DailyPair getDailyPair(Stock s1, Stock s2, Date d) {

		DailyPair dp = null;

		try {

			Connection c = DataSourceUtils.getConnection(dataSource);

			String proc = "{ call hist_data.get_daily_pair(?,?,?) }";
			CallableStatement cs = c.prepareCall(proc);
			cs.setString(1, s1.getSymbol());
			cs.setString(2, s2.getSymbol());
			cs.setDate(3, new java.sql.Date(d.getTime()));
			cs.execute();
			ResultSet rs = cs.getResultSet();

			int rowCt = 0;
			while (rs.next()) {

				if (++rowCt > 1)
					throw new RuntimeException(
							"Only expecting one row from this query");

				double correlation = rs.getDouble("one_day_correlation");
				dp = new DailyPair(s1, s2, d, correlation);
			}

			DataSourceUtils.releaseConnection(c, dataSource);

		} catch (Exception e) {
			throw new RuntimeException(e);
		}

		return dp;
	}
}